
<html><HEAD>
<LINK REL=STYLESHEET HREF="default.css" TYPE="text/css">
<TITLE>
Preparing consolidated databases</TITLE>
</HEAD>
<BODY>

<!-- Header -->
<p class="ancestor" align="right"><A HREF="apptechp76.htm">Previous</A>&nbsp;&nbsp;<A HREF="apptechp78.htm" >Next</A>
<!-- End Header -->
<A NAME="BCGHDGGA"></A><h1>Preparing consolidated databases</h1>
<A NAME="TI1996"></A><p>Whether you are designing a new database or preparing an existing
one to be used as a MobiLink consolidated database, you must install
the MobiLink system tables in that database. <ACRONYM title = "sequel" >SQL</ACRONYM> Anywhere
provides setup scripts for Sybase Adaptive Server Enterprise, Oracle,
Microsoft <ACRONYM title = "sequel" >SQL</ACRONYM> Server, and IBM DB2.
A setup script is not required for SQL Anywhere databases.</p>
<A NAME="TI1997"></A><p>MobiLink system tables store information for MobiLink users,
tables, scripts, and script versions in the consolidated database.
You will probably not directly access these tables, but you alter
them when you perform actions such as adding synchronization scripts.</p>
<A NAME="TI1998"></A><h4>ODBC connections and drivers</h4>
<A NAME="TI1999"></A><p>To carry out synchronization, the MobiLink synchronization
server needs an ODBC connection to the consolidated database. You
must have an ODBC driver for your server and you must create an
ODBC data source for the database on the machine on which your MobiLink
synchronization server is running. For a list of supported drivers,
see <A HREF="http://www.sybase.com/detail?id=1011880">Recommended ODBC Drivers for MobiLink</A>
.</p>
<A NAME="TI2000"></A><h4>Writing synchronization scripts</h4>
<A NAME="TI2001"></A><p>There are two types of events that occur during synchronization
and for which you need to write synchronization scripts:<A NAME="TI2002"></A>
<ul>
<li class=fi><A HREF="apptechp77.htm#BCGGDJDE">Connection events</A> that
perform global tasks required during every synchronization</li>
<li class=ds><A HREF="apptechp77.htm#BCGDIFDD">Table events</A> that
are associated with a specific table and perform tasks related to
modifying data in that table
</li>
</ul>
</p>
<A NAME="BCGGDJDE"></A><h2>Connection events</h2>
<A NAME="TI2003"></A><p>At the connection level, the sequence of major events is as
follows:<p><PRE>begin_connection<br>   begin_synchronization<br>      begin_upload<br>      end_upload<br>      prepare_for_download<br>      begin_download<br>      end_download<br>   end_synchronization<br>end_connection</PRE></p>
</p>
<A NAME="TI2004"></A><p>When a synchronization request occurs, the <b>begin_connection</b> event
is fired. When all synchronization requests for the current script
version have been completed, the <b>end_connection</b> event
is fired. Typically you place initialization and cleanup code in
the scripts for these events, such as variable declaration and database
cleanup.</p>
<A NAME="TI2005"></A><p>Apart from <b>begin_connection</b> and <b>end_connection</b>,
all of these events take the MobiLink user name stored in the <b>ml_user</b> table
in the consolidated database as a parameter. You can use parameters
in your scripts by placing question marks where the parameter value
should be substituted.</p>
<A NAME="TI2006"></A><p>To make scripts in SQL Anywhere databases easier to read,
you might declare a variable in the <b>begin_connection</b> script,
then set it to the value of <b>ml_username</b> in
the <b>begin_synchronization</b> script. </p>
<A NAME="TI2007"></A><p>For example, in <b>begin_connection</b>:<p><PRE> CREATE VARIABLE @sync_user VARCHAR(128);</PRE></p>
<A NAME="TI2008"></A><p>In <b>begin_synchronization</b>:<p><PRE> SET @sync_user = ?</PRE></p>
<A NAME="TI2009"></A><p>The <b>begin_synchronization</b> and <b>end_synchronization</b> events
are fired before and after changes are applied to the remote and
consolidated databases. </p>
<A NAME="TI2010"></A><p>The <b>begin_upload</b> event marks the
beginning of the upload transaction. Applicable inserts and updates
to the consolidated database are performed for all remote tables,
then rows are deleted as applicable for all remote tables. After <b>end_upload</b>,
upload changes are committed.</p>
<A NAME="TI2011"></A><p>If you do not want to delete rows from the consolidated database,
do not write scripts for the <b>upload_delete</b> event,
or use the <b>STOP SYNCHRONIZATION DELETE</b> statement
in your PowerScript code. For more information, see <A HREF="apptechp79.htm#BCGCFGJH">"Deleting rows from the remote
database only"</A>.</p>
<A NAME="TI2012"></A><p>The <b>begin_download</b> event marks
the beginning of the download transaction. Applicable deletes are
performed for all remote tables, and then rows are added as applicable
for all remote tables in the <b>download_cursor</b>.
After <b>end_download</b>, download changes
are committed. These events have the date of the last download as
a parameter.</p>
<A NAME="TI2013"></A><p>Other connection-level events can also occur, such as <b>handle_error</b>, <b>report_error</b>,
and <b>synchronization_statistics</b>. For
a complete list of events and examples of their use, see the chapter
on synchronization events in the <i>MobiLink Administration
Guide</i>
.</p>
<A NAME="BCGDIFDD"></A><h2>Table events</h2>
<A NAME="TI2014"></A><p>Many of the connection events that occur between the <b>begin_synchronization</b> and <b>end_synchronization</b> events,
such as <b>begin_download</b> and <b>end_upload</b>,
also have table equivalents. These and other overall table events
might be used for tasks such as creating an intermediate table to
hold changes or printing information to a log file. </p>
<A NAME="TI2015"></A><p>You can also script table events that apply to each row in
the table. For row-level events, the order of the columns
in your scripts must match the order in which they appear in the <b>CREATE
TABLE</b> statement in the remote database, and the column
names in the scripts must refer to the column names in the consolidated
database.</p>
<A NAME="TI2016"></A><h4>Generating default scripts</h4>
<A NAME="TI2017"></A><p>Although there are several row-level events, most tables need
scripts for three upload events (for <b>INSERT</b>, <b>UPDATE</b>,
and <b>DELETE)</b> and one download event. To speed
up the task of creating these four scripts for every table, you
can generate scripts for them automatically by running the "create
a synchronization model" task from the MobiLink plug-in
in Sybase Central. </p>
<A NAME="TI2018"></A><p>For information on the MobiLink plug-in, see the online <i>MobiLink
Getting Started</i>
 book.</p>
<A NAME="TI2019"></A><p>The MobiLink plug-in allows you to add more functionality
to default scripts than default scripts generated in earlier versions
of MobiLink. However, if you are using ASA 8 or ASA 9 instead of <ACRONYM title = "sequel" >SQL</ACRONYM> Anywhere 10 or 11, you can still generate
default synchronization scripts by starting the MobiLink synchronization
server with the <b>-za</b> switch and setting the SendColumnNames extended
option for <b>dbmlsync</b>. </p>
<A NAME="TI2020"></A><p>The following procedure describes how to generate ASA 8 or
9 synchronizations scripts from the PowerBuilder UI. </p>
<A NAME="TI2021"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To generate ASA 8 or 9 synchronization scripts
automatically from PowerBuilder:</p>
<ol><li class=fi><p>Expand the ODBC Utilities folder in the
Database painter and double-click the MobiLink Synchronization
Server item.</p><p>The MobiLink Synchronize Server Options dialog box displays.</p></li>
<li class=ds><p>Select Adaptive Server Anywhere 8 or 9 from the
MobiLink Version drop-down list.</p><p>You enable the Automatic Script Generation check box.</p></li>
<li class=ds><p>Select the Automatic Script Generation check box
in the MobiLink Synchronize Server Options dialog box and click
OK to start the server.</p><p>You can open this dialog box from the Utilities folder in
the Database painter or the Database Profiles dialog box.</p></li>
<li class=ds><p>In your application, enter <FONT FACE="Courier New">SendColumnNames=ON</FONT> in
the Extended text box on the Settings page of the <b>w_</b><i>appname</i><b>_sync_options</b> window.</p><p>You must have at least one publication, user, and subscription
defined in the remote database. If you have more than one publication
or user, you must use the <b>-n</b> and/or <b>-u</b> switches
to specify which subscription you want to work with.</p><p>If there are existing scripts in the consolidated database,
MobiLink does nothing. If there are no existing scripts, MobiLink
generates them for all tables specified in the publication. The
scripts control the upload and download of data to and from your
client and consolidated databases.</p><p>If the column names on the remote and consolidated database
differ, the generated scripts must be modified to match the names
on the consolidated database.</p></li></ol>
<br><A NAME="TI2022"></A><p>You can also generate ASA 8 or 9 synchronization scripts from
a command prompt. Start the server using the <b>-za</b> switch,
then run <b>dbmlsync</b> and set the SendColumnNames
extended option to <b>on</b>. For example:<p><PRE> dbmlsrv9 -c "dsn=masterdb" -za</PRE><PRE> dbmlsync -c "dsn=remotedb" -e SendColumnNames=ON</PRE></p>
<A NAME="TI2023"></A><h4>Generated scripts</h4>
<A NAME="TI2024"></A><p><A HREF="apptechp77.htm#CACIDJDC">Table 13-3</A> shows
sample default scripts generated by the MobiLink plug-in in Sybase
Central. The scripts are generated for a table named <b>emp</b> with
the columns <b>emp_id</b>, <b>emp_name</b>,
and <b>dept_id</b>. The primary key is <b>emp_id</b>.
The generated download scripts use a timestamp based download.</p>
<A NAME="CACIDJDC"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><caption>Table 13-3: Sample default synchronization
scripts from MobiLink plug-in</caption>
<tr><th  rowspan="1"  ><A NAME="TI2025"></A>Script name</th>
<th  rowspan="1"  ><A NAME="TI2026"></A>Script</th>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2027"></A><b>upload_insert</b></td>
<td  rowspan="1"  ><A NAME="TI2028"></A><FONT FACE="Courier New">INSERT INTO "GROUP1"."emp"
( "emp_id", "emp_name", "dept_id" )VALUES
( {ml r."emp_id"}, {ml r."emp_name"}, {ml
r."dept_id"} )</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2029"></A><b>upload_update</b></td>
<td  rowspan="1"  ><A NAME="TI2030"></A><FONT FACE="Courier New">UPDATE "GROUP1"."emp"
SET "emp_name" = <br>{ml r."emp_name"},
"dept_id" = <br>{ml r."dept_id"}<br>WHERE
"emp_id" = {ml r."emp_id"}</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2031"></A><b>upload_delete</b></td>
<td  rowspan="1"  ><A NAME="TI2032"></A><FONT FACE="Courier New">DELETE FROM "GROUP1"."emp"<br>WHERE
"emp_id" = {ml r."emp_id"}</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2033"></A><b>download_cursor</b></td>
<td  rowspan="1"  ><A NAME="TI2034"></A><FONT FACE="Courier New">SELECT "GROUP1"."emp"."emp_id",    "GROUP1"."emp"."emp_name", "GROUP1"."emp"."dept_id" <br>FROM
"GROUP1"."emp" <br>WHERE "GROUP1"."emp"."last_modified" &gt;= {ml s.last_table_download}</FONT> </td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2035"></A><b>download_delete_cursor</b></td>
<td  rowspan="1"  ><A NAME="TI2036"></A><FONT FACE="Courier New">SELECT "emp_del"."emp_id <br>FROM
"emp_del"<br>WHERE "emp_del"."last_modified" &gt;= {ml s.last_table_download}</FONT></td>
</tr>
</table>
<A NAME="TI2037"></A><p>The scripts that you generate with the MobiLink plug-in constitute
a synchronization model. After you create a synchronization model,
you must use the "Deploy the synchronization model" task
of the plug-in to deploy the scripts to consolidated and remote
databases or to <ACRONYM title = "sequel" >SQL</ACRONYM> files.</p>
<A NAME="TI2038"></A><p><A HREF="apptechp77.htm#BJFBDIAF">Table 13-4</A> shows
the scripts that are generated for the same table using the <b>-za</b> command
switch for the ASA 9 MobiLink synchronization server. The scripts generated
for downloading data perform "snapshot" synchronization.
A complete image of the table is downloaded to the remote database.
Typically you need to edit these scripts to limit the data transferred. </p>
<A NAME="TI2039"></A><p>For more information, see <A HREF="apptechp79.htm#BJFDGGAD">"Limiting data downloads"</A>.</p>
<A NAME="BJFBDIAF"></A><table cellspacing=0 cellpadding=6 border=1 frame="void" rules="all"><caption>Table 13-4: Sample default scripts generated
by dbmlsrv9 -za</caption>
<tr><th  rowspan="1"  ><A NAME="TI2040"></A>Script name</th>
<th  rowspan="1"  ><A NAME="TI2041"></A>Script</th>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2042"></A><b>upload_insert</b></td>
<td  rowspan="1"  ><A NAME="TI2043"></A><FONT FACE="Courier New">INSERT INTO emp (emp_id,
emp_name, dept_id)<br>VALUES (?,?,?)</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2044"></A><b>upload_update</b></td>
<td  rowspan="1"  ><A NAME="TI2045"></A><FONT FACE="Courier New">UPDATE emp SET emp_name = ?,
dept_id = ?<br>WHERE emp_id=?</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2046"></A><b>upload_delete</b></td>
<td  rowspan="1"  ><A NAME="TI2047"></A><FONT FACE="Courier New">DELETE FROM emp<br>WHERE
emp_id=?</FONT></td>
</tr>
<tr><td  rowspan="1"  ><A NAME="TI2048"></A><b>download_cursor</b></td>
<td  rowspan="1"  ><A NAME="TI2049"></A><FONT FACE="Courier New">SELECT emp_id,
emp_name, dept_id FROM emp</FONT></td>
</tr>
</table>
<A NAME="TI2050"></A><p>Before modifying any scripts, you should test the synchronization
process to make sure that the generated scripts behave as expected.
Performing a test after each modification will help you narrow down
errors.</p>
<A NAME="BCGHHBEE"></A><h2>Working with scripts and users in Sybase Central</h2>
<A NAME="TI2051"></A><p>You can view and modify existing scripts and write new ones
in the MobiLink Synchronization plug-in in Sybase Central. These
procedures describe how to connect to the plug-in and write scripts,
and how to add a user to the consolidated database.</p>
<A NAME="TI2052"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To connect to a consolidated database in Sybase
Central:</p>
<ol><li class=fi><p>Start Sybase Central and select Connections&gt;Connect
with MobiLink 11 from the menu bar.</p></li>
<li class=ds><p>On the Identification page in the Connect to Consolidated
Database dialog box, select or browse to a data source name or file,
and click OK.</p></li></ol>
<br><A NAME="TI2053"></A><p>When you expand the node for a consolidated database in the
MobiLink Synchronization plug-in, you see folders with the following
labels: Tables, Connection Scripts, Synchronized Tables, Users,
Versions, and Notifications. All the procedures in this section
begin by opening one of these folders.</p>
<A NAME="TI2054"></A><h4>Script versions</h4>
<A NAME="TI2055"></A><p>Scripts are organized into groups called script versions.
By specifying a particular version, MobiLink clients can select
which set of synchronization scripts is used to process the upload
stream and prepare the download stream. If you want to define different
versions for scripts, you must add a script version to the consolidated
database before you add scripts for it.</p>
<A NAME="TI2056"></A><p>If you create two different versions, make sure that you have
scripts for all required events in both versions.</p>
<A NAME="TI2057"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To add a script version:</p>
<ol><li class=fi><p>Open the Versions folder, then select File&gt;New&gt;Version
from the Sybase Central menu bar.</p></li>
<li class=ds><p>In the Create Script Version wizard, provide a
name for the version and optionally a description, then click Finish.</p><p>Sybase Central creates the new version and gives it a unique
integer identifier.</p></li></ol>
<br><A NAME="TI2058"></A><h4>Adding synchronized tables and scripts</h4>
<A NAME="TI2059"></A><p>Scripts added for connection events are executed for every
synchronization. Scripts added for table events are executed when
a specific table has been modified. You must specify that a table
is synchronized before you can add scripts for it.</p>
<A NAME="TI2060"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To add a table for synchronization:</p>
<ol><li class=fi><p>Open the Synchronized Tables folder and
select File&gt;New&gt;Synchronized Table.</p></li>
<li class=ds><p>Specify a remote table name you want to synchronize
or select a table in the consolidated database that has the same
name as a table in the remote database.</p></li>
<li class=ds><p>Click Finish.</p></li></ol>
<br><A NAME="TI2061"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To add a script to a synchronized table:</p>
<ol><li class=fi><p>Double-click a table name in the Synchronized
Tables folder, then select File&gt;New&gt;Table Script.</p></li>
<li class=ds><p>In the Create Table Script wizard, select the
version for which you want to add a script, select the event you
want to cause the script to execute, and click Next.</p></li>
<li class=ds><p>Choose to create a new script definition and the
language (<ACRONYM title = "sequel" >SQL</ACRONYM>, Java, or <ACRONYM title = "dot net" >.NET</ACRONYM>) in which you want to write
the definition, or select an existing script version that you want
to share for the new script.</p></li>
<li class=ds><p>Click Finish.</p></li>
<li class=ds><p>Type your script in the editor that displays,
then save and close the file.</p><p>For example, if you want to remove rows that have been shipped
from the <b>Order</b> table in a remote database,
you can place the following <b>SELECT</b> statement
in the <b>download_delete_cursor</b> event,
where <b>order_id</b> is the primary key
column. The first parameter to this event is the <b>last_download</b> timestamp.
It is used here to supply the value for a <b>last_modified</b> column:<p><PRE> SELECT order_id<br>  FROM Order<br>WHERE status = 'Shipped'<br>  AND last_modified &gt;= ?</PRE></p><p>For more information about using the <b>download_delete_cursor</b> event,
see the section on "Writing download_delete_cursor
scripts" in the online <i>MobiLink - Server Administration</i>
 book.</p></li></ol>
<br><A NAME="TI2062"></A><p><img src="images/proc.gif" width=17 height=17 border=0 align="bottom" alt="Steps"> To add a connection-level script:</p>
<ol><li class=fi><p>Open the Connection Scripts folder and
select File&gt;New&gt;Connection Script from the menu
bar.</p></li>
<li class=ds><p>Follow steps 2 to 5 in the previous procedure.</p></li></ol>
<br><A NAME="TI2063"></A><h4>Adding users</h4>
<A NAME="TI2064"></A><p>You can add users directly to the <b>ml_user</b> table
in the consolidated database, then provide the user names and optional
passwords to your users. To add a user, open the Users folder, select
File&gt;New&gt;User, and complete the Create User wizard.</p>
<A NAME="TI2065"></A><p>You also have to add at least one user name to each remote
database, as described in <A HREF="apptechp78.htm#BCGBADDB">"Creating MobiLink users"</A>. </p>

